package com.portal.logic;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class ProductDAO {

	//private String ip;
	private Connection connection;
	
	public ProductDAO(Connection connection) {
		//this.ip = ip;
		this.connection = connection;
	}

	public ProductDAO() {
		
	}

	//Create
	public void insert() {
		Product product = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\product.out");
			product = (Product) deserializedObject;
		} catch (ClassNotFoundException cnfe) {
			cnfe.printStackTrace();
		} catch (IOException ioe) {
			ioe.printStackTrace();
		}
		
		PreparedStatement preparedStatement = null;

		try {
			Statement st = connection.createStatement(); 
            st.executeUpdate("INSERT INTO tblProducts " + 
                "VALUES ("+product.getId()+", '"+product.getName()+ "','"+product.getDesc()+"',"+product.getPrice()+","+product.getIdType()+" )"); 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			//ConnectionManager.closeConnection(connection);
		}
	}
	
	public int selectMaxId() {
		Statement statement = null;
		ResultSet resultSet = null;
		int id = 0;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT max(id) as max_val FROM tblProducts");

			while(resultSet.next()){
				id = Integer.parseInt(resultSet.getString("max_val"));
			}

			/*resultSet.close();
			statement.close();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		
		return id;
	}
	
	
	
	
	
	public Product selectById(int id) {
		Product product = new Product();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
 
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM tblProducts WHERE id = ?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
 
            while (resultSet.next()) {
            	product.setId(resultSet.getInt("id"));
            	product.setName(resultSet.getString("name"));
            	product.setDesc(resultSet.getString("desc"));
            	product.setPrice(resultSet.getInt("price"));
            	product.setIdType(resultSet.getInt("id_type"));
            }
 
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //ConnectionManager.closeConnection(connection);
        }
 
        return product;
    }

	public Product selectByName(String name) {
		Product product = new Product();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
 
        try {
            preparedStatement = connection.prepareStatement("SELECT * FROM tblProducts WHERE name = ?");
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
 
            while (resultSet.next()) {
            	product.setId(resultSet.getInt("id"));
            	product.setName(resultSet.getString("name"));
            	product.setDesc(resultSet.getString("desc"));
            	product.setPrice(resultSet.getInt("price"));
            	product.setIdType(resultSet.getInt("id_type"));
            	
            }
 
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //ConnectionManager.closeConnection(connection);
        }
 
        return product;
    }
	
	//Read All
	public List<Product> selectAll() {
		Statement statement = null;
		ResultSet resultSet = null;
		List<Product> products = new LinkedList<Product>();
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("SELECT * FROM tblProducts");

			Product product = null;
			while(resultSet.next()){
				product = new Product();
				product.setId(Integer.parseInt(resultSet.getString("id")));
				product.setName(resultSet.getString("name"));
				product.setDesc(resultSet.getString("desc"));
				product.setPrice(Integer.parseInt(resultSet.getString("price")));
				product.setIdType(Integer.parseInt(resultSet.getString("id_type")));
            	product.setIdType(resultSet.getInt("id_type"));
				
            	products.add(product);
			}

			/*resultSet.close();
			statement.close();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
		return products;
	}

	public void update(/*Product product, int id*/) {
		Product product = null;
		try {
			Object deserializedObject = SerializationUtil.deserialize("c:\\editProduct.out");
			product = (Product) deserializedObject;
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement preparedStatement = null;

		try {
			Statement st = connection.createStatement(); 
			
			String sql ="UPDATE tblProducts SET " + 
	                "name = '"+product.getName()+"', "
	                //+ "desc = '"+product.getDesc()+"', "
	                + "price="+product.getPrice()+" ,"
	                + "id_type="+product.getIdType()+" "
	                + "WHERE id="+product.getId();
			
            st.executeUpdate(sql);
            
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			//ConnectionManager.closeConnection(connection);
		}
	}

	//Delete
	public void delete(int id) {
		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = connection.prepareStatement("DELETE FROM tblProducts WHERE id = ?");
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();


		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			//ConnectionManager.closeConnection(connection);
		}
	}
}
